<?php

namespace app\admin\controller;

use app\admin\model\Orders;
use app\admin\model\User;
use app\admin\service\WorkSpace as WorkSpaceService;
use app\common\controller\Backend;
use app\common\service\OrderStatus;
use fast\Date as FastDate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;

/**
 * 数据统计
 *
 * @icon fa fa-circle-o
 */
class Report extends Backend
{

    /**
     * 查看
     */
    public function index()
    {
        $starttime = FastDate::unixtime('day', -6);
        $endtime = FastDate::unixtime('day', 0, 'end');

        $strstarttime = date('Y-m-d', $starttime);
        $strendtime = date('Y-m-d', $endtime);

        // 营业额统计
        $turnovercolumn = [];
        $turnoverjoinlist = Db::name('orders')
            ->where('status', OrderStatus::COMPLETED)
            ->where('checkout_time', 'between time', [$strstarttime, $strendtime])
            ->field('checkout_time, SUM(amount) as amount , DATE_FORMAT(checkout_time, "%Y-%m-%d") AS checkout_date')
            ->group('checkout_date')
            ->select();

        for ($time = $starttime; $time <= $endtime;) {
            $turnovercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }

        $turnoverlist = array_fill_keys($turnovercolumn, 0);
        foreach ($turnoverjoinlist as $k => $v) {
            $turnoverlist[$v['checkout_date']] = $v['amount'];
        }

        // 用户统计
        $usercolumn = [];
        $newuserjoinlist = Db::name('user')->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $usercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $newuserlist = array_fill_keys($usercolumn, 0);
        foreach ($newuserjoinlist as $k => $v) {
            $newuserlist[$v['join_date']] = $v['nums'];
        }

        // 新增：查询每日用户总数
        $totaluserlist = [];

        // 查询截止到每个日期的累计用户数
        foreach ($usercolumn as $date) {
            $currentEndTime = strtotime($date . ' 23:59:59');

            $totalCount = Db::name('user')
                ->where('jointime', '<=', $currentEndTime)
                ->count();

            $totaluserlist[$date] = $totalCount;
        }

        // 订单统计
        $ordercolumn = [];
        $orderjoinlist = Db::name('orders')
            ->where('order_time', 'between time', [$strstarttime, $strendtime])
            ->field('order_time, status, COUNT(*) as sums , DATE_FORMAT(order_time, "%Y-%m-%d") AS order_date')
            ->group('order_date')
            ->select();

        for ($time = $starttime; $time <= $endtime;) {
            $ordercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }

        $orderlist = array_fill_keys($ordercolumn, 0);
        $effctorderlist = array_fill_keys($ordercolumn, 0);
        foreach ($orderjoinlist as $k => $v) {
            $orderlist[$v['order_date']] = $v['sums'];
            if ($v['status'] == OrderStatus::COMPLETED) {
                $effctorderlist[$v['order_date']] = $v['sums'];
            }
        }

        $ordercount = count($orderlist);
        $effctordercount = count($effctorderlist);
        $orderfulfillmentrate = $effctordercount / $ordercount * 1;

        // 销售排名TOP10
        $topcolumn = [];
        $orderjoinlist = Orders::where('order_time', 'between time', [$strstarttime, $strendtime])
            ->field('order_time, status, COUNT(*) as sums , DATE_FORMAT(order_time, "%Y-%m-%d") AS order_date')
            ->group('order_date')
            ->select();

        $detailList = [
            'dish' => [],
            'setmeal' => [],
        ];
        foreach ($orderjoinlist as $key => $order) {
            $details = $order->details;
            if ($details) {
                foreach ($details as $key => $detail) {
                    $dishId = $detail->dish_id;
                    if ($dishId) {
                        if (!array_key_exists($dishId, $detailList['dish'])) {
                            $detailList['dish'][$dishId] = [
                                'number' => 0,
                                'name' => $detail->name,
                            ];
                        }
                        $detailList['dish'][$dishId]['number'] +=  intval($detail->number);
                    } else {
                        $setmealId = $detail->setmeal_id;
                        if (!array_key_exists($setmealId, $detailList['setmeal'])) {
                            $detailList['setmeal'][$setmealId] = [
                                'number' => 0,
                                'name' => $detail->name,
                            ];
                        }
                        $detailList['setmeal'][$setmealId]['number'] +=  intval($detail->number);
                    }
                }
            }
        }
        $topList = [];
        if (!empty($detailList['dish'])) {
            foreach ($detailList['dish'] as $key => $item) {
                $topList[] = $item;
            }
        }

        if (!empty($detailList['setmeal'])) {
            foreach ($detailList['setmeal'] as $key => $item) {
                $topList[] = $item;
            }
        }


        usort($topList,  function ($a, $b) {
            if ($a['number'] == $b['number']) {
                return 0;
            }
            return ($a['number'] > $b['number']) ? -1 : 1;
        });

        $lastTop = array_slice($topList, 0, 10);

        $topvalues = [];
        foreach ($lastTop as $key => $value) {
            $topcolumn[] = $value['name'];
            $topvalues[] = $value['number'];
        }

        $this->assign('ordercount', $ordercount);
        $this->assign('effctordercount', $effctordercount);
        $this->assign('orderfulfillmentrate', $orderfulfillmentrate);

        $this->assignconfig('turnovercolumn', array_keys($turnoverlist));
        $this->assignconfig('turnoverdata', array_values($turnoverlist));

        $this->assignconfig('usercolumn', array_keys($newuserlist));
        $this->assignconfig('newuserlist', array_values($newuserlist));
        $this->assignconfig('totaluserlist', array_values($totaluserlist));

        $this->assignconfig('ordercolumn', array_keys($orderlist));
        $this->assignconfig('orderlist', array_values($orderlist));
        $this->assignconfig('effctorderlist', array_values($effctorderlist));
        

        $this->assignconfig('topcolumn', $topcolumn);
        $this->assignconfig('toplist', $topvalues);


        return $this->view->fetch();
    }

    /**
     * 用户统计
     *
     */
    public function userStatistics()
    {
        $data = $this->request->get();
        $starttime =  strtotime($data['begin']);
        $endtime =  strtotime($data['end']);
        //  dump($starttime);
        // dump( $endtime);
        // 用户统计
        $usercolumn = [];
        $newuserjoinlist = Db::name('user')->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $usercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $newuserlist = array_fill_keys($usercolumn, 0);
        foreach ($newuserjoinlist as $k => $v) {
            $newuserlist[$v['join_date']] = $v['nums'];
        }

        // 新增：查询每日用户总数
        $totaluserlist = [];

        // 查询截止到每个日期的累计用户数
        foreach ($usercolumn as $date) {
            $currentEndTime = strtotime($date . ' 23:59:59');

            $totalCount = Db::name('user')
                ->where('jointime', '<=', $currentEndTime)
                ->count();

            $totaluserlist[$date] = $totalCount;
        }

        return json([
            'code' => 1,
            'msg' => '',
            'data' => [
                'dateList' => array_keys($newuserlist),
                'newUserList' => array_values($newuserlist),
                'totalUserList' => array_values($totaluserlist),
            ],
        ]);
    }
    
    
        /**
     * 营业额统计
     *
     */
    public function turnoverStatistics()
    {
        $data = $this->request->get();
        $starttime =  strtotime($data['begin']);
        $endtime =  strtotime($data['end']);

        $strstarttime = date('Y-m-d', $starttime);
        $strendtime = date('Y-m-d', $endtime);

        $turnovercolumn = [];
        $turnoverjoinlist = Db::name('orders')
            ->where('status', OrderStatus::COMPLETED)
            ->where('checkout_time', 'between time', [$strstarttime, $strendtime])
            ->field('checkout_time, SUM(amount) as amount , DATE_FORMAT(checkout_time, "%Y-%m-%d") AS checkout_date')
            ->group('checkout_date')
            ->select();

        for ($time = $starttime; $time <= $endtime;) {
            $turnovercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }

        $turnoverlist = array_fill_keys($turnovercolumn, 0);
        foreach ($turnoverjoinlist as $k => $v) {
            $turnoverlist[$v['checkout_date']] = $v['amount'];
        }

        return json([
            'code' => 1,
            'msg' => '',
            'data' => [
                'dateList' => array_keys($turnoverlist),
                'turnoverList' => array_values($turnoverlist),
            ],
        ]);
    }
    
      /**
     * 订单统计
     *
     */
    public function ordersStatistics(){
        $data = $this->request->get();
        $starttime =  strtotime($data['begin']);
        $endtime =  strtotime($data['end']);

        $strstarttime = date('Y-m-d', $starttime);
        $strendtime = date('Y-m-d', $endtime);

         $ordercolumn = [];
        $orderjoinlist = Db::name('orders')
            ->where('order_time', 'between time', [$strstarttime, $strendtime])
            ->field('order_time, status, COUNT(*) as sums , DATE_FORMAT(order_time, "%Y-%m-%d") AS order_date')
            ->group('order_date')
            ->select();

        for ($time = $starttime; $time <= $endtime;) {
            $ordercolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }

        $orderlist = array_fill_keys($ordercolumn, 0);
        $effctorderlist = array_fill_keys($ordercolumn, 0);
        foreach ($orderjoinlist as $k => $v) {
            $orderlist[$v['order_date']] = $v['sums'];
            if ($v['status'] == OrderStatus::COMPLETED) {
                $effctorderlist[$v['order_date']] = $v['sums'];
            }
        }

        $ordercount = count($orderlist);
        $effctordercount = count($effctorderlist);
        $orderfulfillmentrate = $effctordercount / $ordercount * 1;

        return json([
            'code' => 1,
            'msg' => '',
            'data' => [
                'dateList' => array_keys($orderlist),
                'orderCountList' => array_values($orderlist),
                'validOrderCountList' => array_values($effctorderlist),
            ]
        ]);
    }
    
     /**
     * 查询销量排名top10
     *
     */
    public function top10()
    {
        $data = $this->request->get();
        $starttime =  strtotime($data['begin']);
        $endtime =  strtotime($data['end']);

        $strstarttime = date('Y-m-d', $starttime);
        $strendtime = date('Y-m-d', $endtime);


        // 销售排名TOP10
        $topcolumn = [];
        $orderjoinlist = Orders::where('order_time', 'between time', [$strstarttime, $strendtime])
            ->field('order_time, status, COUNT(*) as sums , DATE_FORMAT(order_time, "%Y-%m-%d") AS order_date')
            ->group('order_date')
            ->select();

        $detailList = [
            'dish' => [],
            'setmeal' => [],
        ];
        foreach ($orderjoinlist as $key => $order) {
            $details = $order->details;
            if ($details) {
                foreach ($details as $key => $detail) {
                    $dishId = $detail->dish_id;
                    if ($dishId) {
                        if (!array_key_exists($dishId, $detailList['dish'])) {
                            $detailList['dish'][$dishId] = [
                                'number' => 0,
                                'name' => $detail->name,
                            ];
                        }
                        $detailList['dish'][$dishId]['number'] +=  intval($detail->number);
                    } else {
                        $setmealId = $detail->setmeal_id;
                        if (!array_key_exists($setmealId, $detailList['setmeal'])) {
                            $detailList['setmeal'][$setmealId] = [
                                'number' => 0,
                                'name' => $detail->name,
                            ];
                        }
                        $detailList['setmeal'][$setmealId]['number'] +=  intval($detail->number);
                    }
                }
            }
        }
        $topList = [];
        if (!empty($detailList['dish'])) {
            foreach ($detailList['dish'] as $key => $item) {
                $topList[] = $item;
            }
        }

        if (!empty($detailList['setmeal'])) {
            foreach ($detailList['setmeal'] as $key => $item) {
                $topList[] = $item;
            }
        }


        usort($topList,  function ($a, $b) {
            if ($a['number'] == $b['number']) {
                return 0;
            }
            return ($a['number'] > $b['number']) ? -1 : 1;
        });

        $lastTop = array_slice($topList, 0, 10);

        $topvalues = [];
        foreach ($lastTop as $key => $value) {
            $topcolumn[] = $value['name'];
            $topvalues[] = $value['number'];
        }

        return json([
            'code' => 1,
            'msg' => '',
            'data' => [
                'nameList' => $topcolumn,
                'numberList' => $topvalues,
            ]
        ]);
    }
    
    /**
     * 导出运营数据
     *
     */
    public function export()
    {
        $begin = FastDate::unixtime('day', -30);
        $end = FastDate::unixtime('day', 0, 'end');

        $startTime = date('Y-m-d', $begin);
        $endTime = date('Y-m-d', $end);

        $workSpace = new WorkSpaceService;
        $businessData = $workSpace->getBusinessData($begin, $end);
        // dump($businessData);
        // exit;
        // 直接加载模板
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load("template/运营数据报表模板.xlsx");

        // 设置活动工作表
        $spreadsheet->setActiveSheetIndex(0);

        // 添加时间
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B2', '时间：' . $startTime . '至' . $endTime);
        $sheet->setCellValue('C4', $businessData['turnoverAmount']);
        $sheet->setCellValue('E4', $businessData['orderFulfillmentRate']);
        $sheet->setCellValue('G4', $businessData['newUserCount']);
        $sheet->setCellValue('C5', $businessData['validOrderCount']);
        $sheet->setCellValue('E5', $businessData['averageRoomRate']);

        for ($i = 0; $i < 30; $i++) {
            $begin = FastDate::unixtime('day', -$i);
            $end = FastDate::unixtime('day', -$i+1, 'end');
            $time = date('Y-m-d', $begin);
            $businessData = $workSpace->getBusinessData($begin, $end);
            $row = 8 + $i;
            $sheet->setCellValue('B' . $row,$time);
            $sheet->setCellValue('C' . $row,$businessData['turnoverAmount']);
            $sheet->setCellValue('D' . $row,$businessData['validOrderCount']);
            $sheet->setCellValue('E' . $row,$businessData['orderFulfillmentRate']);
            $sheet->setCellValue('F' . $row,$businessData['averageRoomRate']);
            $sheet->setCellValue('G' . $row,$businessData['newUserCount']);
        }

        $uploadDir = ROOT_PATH . 'public' . DS;
        $outputFilename = '近30天运营数据_' . date('YmdHis') . '.xlsx';
        $outputPath = $uploadDir . $outputFilename;

        // 保存工作簿
        $writer = new Xlsx($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        $writer->save($outputPath);

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $outputFilename . '"');
        header('Cache-Control: max-age=0');
        readfile($outputPath);
        unlink($outputPath);
    }
}
